﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using FuzzyMatching;
namespace Convert.CPIAMmodel
{
    public class StockFile
    {

        public StockFile(Guid guidStkID, int intStkNumber, string strStkName, string strMfc, int intDeptNo, decimal decStkWSGSTPerc,
            decimal decPrice1, short decQty1, decimal decPrice2, short decQty2, decimal decPrice3, short decQty3, decimal decPrice4, short decQty4, decimal decPrice5, short decQty5, 
            string strExternPDE, int intSupID, decimal dblSOH, short shtMinStk, int intMinOrdQty, Boolean blnStockActive, Boolean blnEthical, Boolean blnPrintLab, Boolean blnS3,
            Boolean blnOrdStock, decimal decStkPrevInvCost, decimal decStkLastInvCost, decimal decAvgCost, decimal decAvgCostTotal,
            decimal decCost, string strGSTCode, decimal decGSTPerc, decimal decMUP, decimal decExTaxSell, decimal decGSTAmt, decimal decGSTIncSell, short shtPackSize,Boolean blnDiscable,
            Double StkPurCurrMth, Decimal StkCostCurrMth, Double StkCredCurrMth, Decimal StkCredCostCurrMth, Double StkSoldCurrMth, Decimal StkSellCurrMth, Decimal StkCOGSCurrMth)
        {
            
            this.StkID = guidStkID;
            this.StkNumber = intStkNumber;
            this.StkName = strStkName;
            this.StkCreation = DateTime.Now;
            this.StkMfc = strMfc;
            this.DeptNo = intDeptNo;
            this.StkManufCode = "ZZZ";
            this.StkWSLast = new DateTime(1900, 1, 1);
            this.StkWSGSTFlag = "N";
            this.StkMTCode = "Z";
            this.StkGSTPerc = decGSTPerc;
            this.StkPrice1 = decPrice1;
            this.StkPrice2 = decPrice2;
            this.StkPrice3 = decPrice3;
            this.StkPrice4 = decPrice4;
            this.StkPrice5 = decPrice5;
            this.StkUnits1 = decQty1;
            this.StkUnits2 = decQty2;
            this.StkUnits3 = decQty3;
            this.StkUnits4 = decQty4;
            this.StkUnits5 = decQty5;
            this.StkExternalDispPde = strExternPDE;
            this.SupplierID = intSupID;
            this.StkOnHand = dblSOH;
            this.StkMinLevel = shtMinStk;
            this.StkOrderQty = intMinOrdQty;
            this.StkActive = blnStockActive;
            this.StkEthical = blnEthical;
            this.StkPrintLabel = blnPrintLab;
            this.StkS3 = blnS3;
            this.StkSetPrice = false;
            this.StkLabelType = "THA";
            this.StkTakeLastDate = DateTime.Now;
            this.StkTakeFig = 0;
            this.StkOrdStock = blnOrdStock;
            this.StkPrevInvCost = decStkPrevInvCost;
            this.StkLastInvCost = decStkLastInvCost;
            this.StkLastInvNo = "N/A";
            this.StkLastSoldDate = DateTime.Now;
            this.StkLastInvDate = DateTime.Now;
            this.StkAvgCost = decAvgCost;
            this.StkAvgTotal = decAvgCostTotal;
            this.StkActiveCostFlag = 1;
            this.StkActiveCostPrice = decCost;
            this.StkGSTCode = strGSTCode;
            this.StkGSTPerc = decGSTPerc;
            this.StkSpecialMp = decMUP;
            this.StkSellExTaxPrice = decExTaxSell;
            this.StkGSTAmt = decGSTAmt;
            this.StkSellPrice = decGSTIncSell;
            this.StkPackSize = shtPackSize;
            this.StkDiscable = blnDiscable;
            this.StkPurCurrMth = StkPurCurrMth;
            this.StkCostCurrMth = StkCostCurrMth;
            this.StkCredCurrMth = StkCredCurrMth;
            this.StkCredCostCurrMth = StkCredCostCurrMth;
            this.StkSoldCurrMth = StkSoldCurrMth;
            this.StkSellCurrMth = StkSellCurrMth;
            this.StkCOGSCurrMth = StkCOGSCurrMth;
            this.ShopSOH = dblSOH;
        }

        private Guid _StkID;

        public Guid StkID
        {
            get { return _StkID; }
            set { _StkID = value; }
        }
        private int _StkNumber;

        public int StkNumber
        {
            get { return _StkNumber; }
            set { _StkNumber = value; }
        }
        private string _StkName;

        public string StkName
        {
            get { return _StkName; }
            set
            {
                _StkName = string.IsNullOrWhiteSpace(value) ? "" : value;
                if (_StkName.Length > 100)
                    _StkName = _StkName.Substring(0, 100);
            }
        }
        private DateTime _StkCreation;

        public DateTime StkCreation
        {
            get { return _StkCreation; }
            set { _StkCreation = value; }
        }

        private string _StkMfc;

        public string StkMfc
        {
            get { return _StkMfc; }
            set { _StkMfc = value; }
        }

        private int _DeptNo;

        public int DeptNo
        {
            get { return _DeptNo; }
            set { _DeptNo = value; }
        }
        private string _StkManufCode;

        public string StkManufCode
        {
            get { return _StkManufCode; }
            set { _StkManufCode = value; }
        }

        private DateTime _StkWSLast;

        public DateTime StkWSLast
        {
            get { return _StkWSLast; }
            set { _StkWSLast = value; }
        }

        private string _StkMTCode;

        public string StkMTCode
        {
            get { return _StkMTCode; }
            set { _StkMTCode = value; }
        }

        private string _StkWSGSTFlag;

        public string StkWSGSTFlag
        {
          get { return _StkWSGSTFlag; }
          set { _StkWSGSTFlag = value; }
        }
                private decimal _StkWSGSTPerc;

        public decimal StkWSGSTPerc
        {
          get { return _StkWSGSTPerc; }
          set { _StkWSGSTPerc = value; }
        }
        private decimal _StkPrice1;

        public decimal StkPrice1
        {
            get { return _StkPrice1; }
            set { _StkPrice1 = value; }
        }
        private short _StkUnits1;

        public short StkUnits1
        {
            get { return _StkUnits1; }
            set { _StkUnits1 = value; }
        }

        private decimal _StkPrice2;

        public decimal StkPrice2
        {
            get { return _StkPrice2; }
            set { _StkPrice2 = value; }
        }
        private short _StkUnits2;

        public short StkUnits2
        {
            get { return _StkUnits2; }
            set { _StkUnits2 = value; }
        }

        private decimal _StkPrice3;

        public decimal StkPrice3
        {
            get { return _StkPrice3; }
            set { _StkPrice3 = value; }
        }
        private short _StkUnits3;

        public short StkUnits3
        {
            get { return _StkUnits3; }
            set { _StkUnits3 = value; }
        }

        private decimal _StkPrice4;

        public decimal StkPrice4
        {
            get { return _StkPrice4; }
            set { _StkPrice4 = value; }
        }
        private short _StkUnits4;

        public short StkUnits4
        {
            get { return _StkUnits4; }
            set { _StkUnits4 = value; }
        }

        private decimal _StkPrice5;

        public decimal StkPrice5
        {
            get { return _StkPrice5; }
            set { _StkPrice5 = value; }
        }
        private short _StkUnits5;

        public short StkUnits5
        {
            get { return _StkUnits5; }
            set { _StkUnits5 = value; }
        }

        private string _StkExternalDispPde;

        public string StkExternalDispPde
        {
            get { return _StkExternalDispPde; }
            set { _StkExternalDispPde = value; }
        }

        private decimal _StkOnHand;

        public decimal StkOnHand
        {
            get { return _StkOnHand; }
            set { _StkOnHand = Math.Round(value, 2, MidpointRounding.AwayFromZero); }
        }
        private int _StkMinLevel;

        public int StkMinLevel
        {
            get { return _StkMinLevel; }
            set { _StkMinLevel = value; }
        }
        private int _StkOrderQty;

        public int StkOrderQty
        {
            get { return _StkOrderQty; }
            set { _StkOrderQty = value; }
        }
        private Boolean _StkActive;

        public Boolean StkActive
        {
            get { return _StkActive; }
            set { _StkActive = value; }
        }

        private Boolean _StkEthical;

        public Boolean StkEthical
        {
            get { return _StkEthical; }
            set { _StkEthical = value; }
        }

        private Boolean _StkPrintLabel;

        public Boolean StkPrintLabel
        {
            get { return _StkPrintLabel; }
            set { _StkPrintLabel = value; }
        }

        private Boolean _StkS3;

        public Boolean StkS3
        {
            get { return _StkS3; }
            set { _StkS3 = value; }
        }

        Boolean _StkSetPrice;

        public Boolean StkSetPrice
        {
            get { return _StkSetPrice; }
            set { _StkSetPrice = value; }
        }

        private string _StkLabelType;

        public string StkLabelType
        {
            get { return _StkLabelType; }
            set { _StkLabelType = value; }
        }

        private DateTime _StkTakeLastDate;

        public DateTime StkTakeLastDate
        {
            get { return _StkTakeLastDate; }
            set { _StkTakeLastDate = value; }
        }

        private double _StkTakeFig;

        public double StkTakeFig
        {
            get { return _StkTakeFig; }
            set { _StkTakeFig = value; }
        }

        private Boolean _StkOrdStock;

        public Boolean StkOrdStock
        {
            get { return _StkOrdStock; }
            set { _StkOrdStock = value; }
        }
        private Decimal _StkPrevInvCost;

        public Decimal StkPrevInvCost
        {
            get { return _StkPrevInvCost; }
            set { _StkPrevInvCost = value; }
        }
        private Decimal _StkLastInvCost;

        public Decimal StkLastInvCost
        {
            get { return _StkLastInvCost; }
            set { _StkLastInvCost = value; }
        }
        private DateTime _StkLastInvDate;

        public DateTime StkLastInvDate
        {
            get { return _StkLastInvDate; }
            set { _StkLastInvDate = value; }
        }

        private string _StkLastInvNo;

        public string StkLastInvNo
        {
            get { return _StkLastInvNo; }
            set { _StkLastInvNo = value; }
        }

        private DateTime _StkLastSoldDate;

        public DateTime StkLastSoldDate
        {
            get { return _StkLastSoldDate; }
            set { _StkLastSoldDate = value; }
        }
        private Decimal _StkAvgCost;

        public Decimal StkAvgCost
        {
            get { return _StkAvgCost; }
            set { _StkAvgCost = value; }
        }
        private Decimal _StkAvgTotal;

        public Decimal StkAvgTotal
        {
            get { return _StkAvgTotal; }
            set { _StkAvgTotal = value; }
        }
        private Decimal _StkActiveCostPrice;

        public Decimal StkActiveCostPrice
        {
            get { return _StkActiveCostPrice; }
            set { _StkActiveCostPrice = value; }
        }
        private Byte _StkActiveCostFlag;

        public Byte StkActiveCostFlag
        {
            get { return _StkActiveCostFlag; }
            set { _StkActiveCostFlag = value; }
        }

        private string _StkGSTCode;

        public string StkGSTCode
        {
            get { return _StkGSTCode; }
            set { _StkGSTCode = value; }
        }
        private Decimal _StkGSTPerc;

        public Decimal StkGSTPerc
        {
            get { return _StkGSTPerc; }
            set { _StkGSTPerc = value; }
        }
        private Decimal _StkSpecialMp;

        public Decimal StkSpecialMp
        {
            get { return _StkSpecialMp; }
            set { _StkSpecialMp = value; }
        }
        private Decimal _StkSellExTaxPrice;

        public Decimal StkSellExTaxPrice
        {
            get { return _StkSellExTaxPrice; }
            set { _StkSellExTaxPrice = value; }
        }
        private Decimal _StkGSTAmt;

        public Decimal StkGSTAmt
        {
            get { return _StkGSTAmt; }
            set { _StkGSTAmt = value; }
        }
        private Decimal _StkSellPrice;

        public Decimal StkSellPrice
        {
            get { return _StkSellPrice; }
            set { _StkSellPrice = value; }
        }

        private short _StkPackSize;

        public short StkPackSize
        {
            get { return _StkPackSize; }
            set { _StkPackSize = value; }
        }
        private Boolean _StkDiscable;

        public Boolean StkDiscable
        {
            get { return _StkDiscable; }
            set { _StkDiscable = value; }
        }
        private int _SupplierID;

        public int SupplierID
        {
            get { return _SupplierID; }
            set { _SupplierID = value; }
        }

        private Double _StkPurCurrMth;

        public Double StkPurCurrMth
        {
            get { return _StkPurCurrMth; }
            set { _StkPurCurrMth = value; }
        }
        private Decimal _StkCostCurrMth;

        public Decimal StkCostCurrMth
        {
            get { return _StkCostCurrMth; }
            set { _StkCostCurrMth = value; }
        }
        private Double _StkCredCurrMth;

        public Double StkCredCurrMth
        {
            get { return _StkCredCurrMth; }
            set { _StkCredCurrMth = value; }
        }
        private Decimal _StkCredCostCurrMth;

        public Decimal StkCredCostCurrMth
        {
            get { return _StkCredCostCurrMth; }
            set { _StkCredCostCurrMth = value; }
        }
        private Double _StkSoldCurrMth;

        public Double StkSoldCurrMth
        {
            get { return _StkSoldCurrMth; }
            set { _StkSoldCurrMth = value; }
        }
        private Decimal _StkSellCurrMth;

        public Decimal StkSellCurrMth
        {
            get { return _StkSellCurrMth; }
            set { _StkSellCurrMth = value; }
        }
        private Decimal _StkCOGSCurrMth;

        public Decimal StkCOGSCurrMth
        {
            get { return _StkCOGSCurrMth; }
            set { _StkCOGSCurrMth = value; }
        }
        private Double _StkUnitsCurrMthTrans;

        public Double StkUnitsCurrMthTrans
        {
            get { return _StkUnitsCurrMthTrans; }
            set { _StkUnitsCurrMthTrans = value; }
        }
        private Decimal _StkCostCurrMthTrans;

        public Decimal StkCostCurrMthTrans
        {
            get { return _StkCostCurrMthTrans; }
            set { _StkCostCurrMthTrans = value; }
        }
        private Double _StkBonusCurrMth;

        public Double StkBonusCurrMth
        {
            get { return _StkBonusCurrMth; }
            set { _StkBonusCurrMth = value; }
        }
        private Decimal _StkBonusValCurrMth;

        public Decimal StkBonusValCurrMth
        {
            get { return _StkBonusValCurrMth; }
            set { _StkBonusValCurrMth = value; }
        }
        private Double _StkShrinkCurrMth;

        public Double StkShrinkCurrMth
        {
            get { return _StkShrinkCurrMth; }
            set { _StkShrinkCurrMth = value; }
        }
        private Decimal _StkShrinkValCurrMth;

        public Decimal StkShrinkValCurrMth
        {
            get { return _StkShrinkValCurrMth; }
            set { _StkShrinkValCurrMth = value; }
        }
        private Double _StkWasteCurrMth;

        public Double StkWasteCurrMth
        {
            get { return _StkWasteCurrMth; }
            set { _StkWasteCurrMth = value; }
        }
        private Decimal _StkWasteValCurrMth;

        public Decimal StkWasteValCurrMth
        {
            get { return _StkWasteValCurrMth; }
            set { _StkWasteValCurrMth = value; }
        }
        private Boolean _StkInClub;

        public Boolean StkInClub
        {
            get { return _StkInClub; }
            set { _StkInClub = value; }
        }
        private Decimal _StkClubSellPrice;

        public Decimal StkClubSellPrice
        {
            get { return _StkClubSellPrice; }
            set { _StkClubSellPrice = value; }
        }
        private Boolean _StkCustDisplay;

        public Boolean StkCustDisplay
        {
            get { return _StkCustDisplay; }
            set { _StkCustDisplay = value; }
        }
        private Boolean _StkBulkFood;

        public Boolean StkBulkFood
        {
            get { return _StkBulkFood; }
            set { _StkBulkFood = value; }
        }
        private Boolean _StkAvailWH;

        public Boolean StkAvailWH
        {
            get { return _StkAvailWH; }
            set { _StkAvailWH = value; }
        }
        private String _StkRegisterMsg;

        public String StkRegisterMsg
        {
            get { return _StkRegisterMsg; }
            set { _StkRegisterMsg = value; }
        }
        private Boolean _StkPriceORide;

        public Boolean StkPriceORide
        {
            get { return _StkPriceORide; }
            set { _StkPriceORide = value; }
        }
        private Boolean _StkIsSystem;

        public Boolean StkIsSystem
        {
            get { return _StkIsSystem; }
            set { _StkIsSystem = value; }
        }
        private Boolean _StkIsHidden;

        public Boolean StkIsHidden
        {
            get { return _StkIsHidden; }
            set { _StkIsHidden = value; }
        }

        private decimal _ShopSOH;

        public decimal ShopSOH
        {
            get { return _ShopSOH; }
            set { _ShopSOH = Math.Round(value, 2, MidpointRounding.AwayFromZero); }
        } 


        public static void SaveToDateTable(StockFile stockFile, DataTable StockFileDT)
        {
            StockFileDT.Rows.Add(
                stockFile.StkID, stockFile.StkNumber, stockFile.StkName, stockFile.StkCreation,stockFile.StkMfc, stockFile.DeptNo,stockFile.StkManufCode, stockFile.StkWSLast, stockFile.StkMTCode, stockFile.StkWSGSTFlag, 
                stockFile.StkWSGSTPerc, stockFile.StkPrice1, stockFile.StkUnits1, stockFile.StkPrice2, stockFile.StkUnits2, stockFile.StkPrice3, stockFile.StkUnits3, stockFile.StkPrice4, stockFile.StkUnits4, stockFile.StkPrice5, 
                stockFile.StkUnits5, stockFile.StkExternalDispPde, stockFile.SupplierID, stockFile.StkOnHand, stockFile.StkMinLevel, stockFile.StkOrderQty, stockFile.StkActive, stockFile.StkEthical, stockFile.StkPrintLabel, stockFile.StkS3,
                stockFile.StkSetPrice,  stockFile.StkLabelType, stockFile.StkTakeLastDate, stockFile.StkTakeFig, stockFile.StkOrdStock, stockFile.StkPrevInvCost, stockFile.StkLastInvCost, stockFile.StkLastInvDate, stockFile.StkLastInvNo, stockFile.StkLastSoldDate,
                stockFile.StkAvgCost, stockFile.StkAvgTotal, stockFile.StkActiveCostFlag, stockFile.StkActiveCostPrice, stockFile.StkGSTCode, stockFile.StkGSTPerc, stockFile.StkSpecialMp, stockFile.StkSellExTaxPrice, stockFile.StkGSTAmt, stockFile.StkSellPrice,
                stockFile.StkPackSize, stockFile.StkDiscable, stockFile.StkPurCurrMth, stockFile.StkCostCurrMth, stockFile.StkCredCurrMth, stockFile.StkCredCostCurrMth, stockFile.StkSoldCurrMth, stockFile.StkSellCurrMth, stockFile.StkCOGSCurrMth, stockFile.ShopSOH);
        }

        public static void CreateTempUpdateStockTable(string connectionString, string TempUpdatedStockTableName)
        {
            if (DbTableExists(TempUpdatedStockTableName, connectionString))
                DropTable(TempUpdatedStockTableName, connectionString);

            String createQuery = @"CREATE TABLE [dbo].[LOTS_UpdatedStockFiles](
	                        [StkID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	                        [StkNumber] [int] NOT NULL,
	                        [StkName] [varchar](100) NOT NULL,
	                        [StkCreation] [datetime] NOT NULL,
	                        [StkMfc] [varchar](50) NOT NULL,
	                        [DeptNo] [int] NOT NULL,
	                        [StkManufCode] [varchar](50) NOT NULL,
	                        [StkWSLast] [datetime] NOT NULL,
	                        [StkMTCode] [varchar](50) NOT NULL,
	                        [StkWSGSTFlag] [varchar](50) NOT NULL,
	                        [StkWSGSTPerc] [decimal](18, 4) NOT NULL,
	                        [StkPrice1] [decimal](18, 4) NOT NULL,
	                        [StkUnits1] [smallint] NOT NULL,
	                        [StkPrice2] [decimal](18, 4) NOT NULL,
	                        [StkUnits2] [smallint] NOT NULL,
	                        [StkPrice3] [decimal](18, 4) NOT NULL,
	                        [StkUnits3] [smallint] NOT NULL,
	                        [StkPrice4] [decimal](18, 4) NOT NULL,
	                        [StkUnits4] [smallint] NOT NULL,
	                        [StkPrice5] [decimal](18, 4) NOT NULL,
	                        [StkUnits5] [smallint] NOT NULL,
	                        [StkExternalDispPde] [varchar](50) NOT NULL,
	                        [SupplierID] [int] NOT NULL,
	                        [StkOnHand] [float] NOT NULL,
	                        [StkMinLevel] [smallint] NOT NULL,
	                        [StkOrderQty] [int] NOT NULL,
	                        [StkActive] [bit] NOT NULL,
	                        [StkEthical] [bit] NOT NULL,
	                        [StkPrintLabel] [bit] NOT NULL,
	                        [StkS3] [bit] NOT NULL,
	                        [StkSetPrice] [bit] NOT NULL,
	                        [StkLabelType] [varchar](50) NOT NULL,
	                        [StkTakeLastDate] [datetime] NOT NULL,
	                        [StkTakeFig] [float] NOT NULL,
	                        [StkOrdStock] [bit] NOT NULL,
	                        [StkPrevInvCost] [decimal](18, 2) NOT NULL,
	                        [StkLastInvCost] [decimal](18, 2) NOT NULL,
	                        [StkLastInvDate] [datetime] NOT NULL,
	                        [StkLastInvNo] [varchar](50) NOT NULL,
	                        [StkLastSoldDate] [datetime] NOT NULL,
	                        [StkAvgCost] [decimal](18, 2) NOT NULL,
	                        [StkAvgTotal] [decimal](18, 2) NOT NULL,
	                        [StkActiveCostFlag] [tinyint] NOT NULL,
	                        [StkActiveCostPrice] [decimal](18, 2) NOT NULL,
	                        [StkGSTCode] [nvarchar](1) NOT NULL,
	                        [StkGSTPerc] [decimal](18, 4) NOT NULL,
	                        [StkSpecialMp] [decimal](18, 4) NOT NULL,
	                        [StkSellExTaxPrice] [decimal](18, 2) NOT NULL,
	                        [StkGSTAmt] [decimal](18, 2) NOT NULL,
	                        [StkSellPrice] [decimal](18, 2) NOT NULL,
	                        [StkPackSize] [smallint] NOT NULL,
	                        [StkDiscable] [bit] NOT NULL,
                            [StkPurCurrMth] [float] NOT NULL,
	                        [StkCostCurrMth] [decimal](18, 2) NOT NULL,
	                        [StkCredCurrMth] [float] NOT NULL,
	                        [StkCredCostCurrMth] [decimal](18, 2) NOT NULL,
	                        [StkSoldCurrMth] [float] NOT NULL,
	                        [StkSellCurrMth] [decimal](18, 2) NOT NULL,
	                        [StkCOGSCurrMth] [decimal](18, 2) NOT NULL,
                            [ShopSOH] [decimal](18, 4) NOT NULL
                         CONSTRAINT [PK_LOTS_UpdatedStockFiles] PRIMARY KEY NONCLUSTERED 
                        (
	                        [StkID] ASC
                        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
                        ) ON [PRIMARY]";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(createQuery, connection);
                command.CommandTimeout = 3600;
                command.CommandType = CommandType.Text;
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

            }
        }

        public static void CreateTempReallocationStockTable(string connectionString, string TempReallocationsStockTableName)
        {
            if (DbTableExists(TempReallocationsStockTableName, connectionString))
                DropTable(TempReallocationsStockTableName, connectionString);

            String createQuery = @"CREATE TABLE [dbo].[LOTS_ReallocatedStockFiles](
	                                    [StkID] [uniqueidentifier] NOT NULL,
	                                    [NewStkNumber] [int] NOT NULL,
                                     CONSTRAINT [PK_LOTS_ReallocatedStockFiles] PRIMARY KEY CLUSTERED 
                                    (
	                                    [StkID] ASC
                                    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
                                    ) ON [PRIMARY]";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(createQuery, connection);
                command.CommandTimeout = 3600;
                command.CommandType = CommandType.Text;
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
        }

        public static Boolean DropTable(string tableName, String connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string strCheckTable = String.Format("DROP TABLE {0}", tableName);
                SqlCommand command = new SqlCommand(strCheckTable, connection);
                command.CommandTimeout = 3600;
                command.CommandType = CommandType.Text;
                connection.Open();
                return System.Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public static bool DbTableExists(string tableName, string strConnection)
        {
            using (SqlConnection connection = new SqlConnection(strConnection))
            {
                string strCheckTable = String.Format("IF OBJECT_ID('{0}', 'U') IS NOT NULL SELECT 'true' ELSE SELECT 'false'", tableName);
                SqlCommand command = new SqlCommand(strCheckTable, connection);
                command.CommandTimeout = 3600;
                command.CommandType = CommandType.Text;
                connection.Open();
                return System.Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public static void PopulateStockNames(string cpiamConnString, Dictionary<Guid, string> StockNames)
        {
            String query = @"SELECT StkID, StkName from StockFile";
            SqlConnection conn = null;
            SqlDataReader reader = null;
            conn = new SqlConnection(cpiamConnString);
            conn.Open();

            SqlCommand command = new SqlCommand(query, conn);
            command.CommandTimeout = 3600;
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                Guid id = reader.GetGuid(0);
                string name = reader.GetString(1);

                StockNames[id] = name;
            }

            if (reader != null)
                reader.Close();

            if (conn != null)
                conn.Close();
        }

        public static void UpdateStockFiles(string connectionString)
        {
            String update = @"UPDATE [StockFile]
                           SET [StkNumber] = U.StkNumber
                              ,[StkName] = U.StkName
                              ,[StkCreation] = U.StkCreation
                              ,[StkMfc] = U.StkMfc
                              ,[DeptNo] = U.DeptNo
                              ,[StkManufCode] = U.StkManufCode
                              ,[StkWSLast] = U.StkWSLast
                              ,[StkMTCode] = U.StkMTCode
                              ,[StkWSGSTFlag] = U.StkWSGSTFlag
                              ,[StkWSGSTPerc] = U.StkWSGSTPerc
                              ,[StkPrice1] = U.StkPrice1
                              ,[StkUnits1] = U.StkUnits1
                              ,[StkPrice2] = U.StkPrice2
                              ,[StkUnits2] = U.StkUnits2
                              ,[StkPrice3] = U.StkPrice3
                              ,[StkUnits3] = U.StkUnits3
                              ,[StkPrice4] = U.StkPrice4
                              ,[StkUnits4] = U.StkUnits4
                              ,[StkPrice5] = U.StkPrice5
                              ,[StkUnits5] = U.StkUnits5
                              ,[StkExternalDispPde] = U.StkExternalDispPde
                              ,[SupplierID] = U.SupplierID
                              ,[StkOnHand] = U.StkOnHand
                              ,[StkMinLevel] = U.StkMinLevel
                              ,[StkOrderQty] = U.StkOrderQty
                              ,[StkActive] = U.StkActive
                              ,[StkEthical] = U.StkEthical
                              ,[StkPrintLabel] = U.StkPrintLabel
                              ,[StkS3] = U.StkS3
                              ,[StkSetPrice] = U.StkSetPrice
                              ,[StkLabelType] = U.StkLabelType
                              ,[StkTakeLastDate] = U.StkTakeLastDate
                              ,[StkTakeFig] = U.StkTakeFig
                              ,[StkOrdStock] = U.StkOrdStock
                              ,[StkPrevInvCost] = U.StkPrevInvCost
                              ,[StkLastInvCost] = U.StkLastInvCost
                              ,[StkLastInvDate] = U.StkLastInvDate
                              ,[StkLastInvNo] = U.StkLastInvNo
                              ,[StkLastSoldDate] = U.StkLastSoldDate
                              ,[StkAvgCost] = U.StkAvgCost
                              ,[StkAvgTotal] = U.StkAvgTotal
                              ,[StkActiveCostFlag] = U.StkActiveCostFlag
                              ,[StkActiveCostPrice] = U.StkActiveCostPrice
                              ,[StkGSTCode] = U.StkGSTCode
                              ,[StkGSTPerc] = U.StkGSTPerc
                              ,[StkSpecialMp] = U.StkSpecialMp
                              ,[StkSellExTaxPrice] = U.StkSellExTaxPrice
                              ,[StkGSTAmt] = U.StkGSTAmt
                              ,[StkSellPrice] = U.StkSellPrice
                              ,[StkPackSize] = U.StkPackSize
                              ,[StkDiscable] = U.StkDiscable
                              ,[StkPurCurrMth] = U.StkPurCurrMth
                              ,[StkCostCurrMth] = U.StkCostCurrMth
                              ,[StkCredCurrMth] = U.StkCredCurrMth
                              ,[StkCredCostCurrMth] = U.StkCredCostCurrMth
                              ,[StkSoldCurrMth] = U.StkSoldCurrMth
                              ,[StkSellCurrMth] = U.StkSellCurrMth
                              ,[StkCOGSCurrMth] = U.StkCOGSCurrMth
                              ,[ShopSOH] = U.ShopSOH
                        FROM StockFile S inner join LOTS_UpdatedStockFiles U
                        on S.StkID = U.StkID";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {

                SqlCommand command = new SqlCommand(update, connection);
                command.CommandTimeout = 3600;
                command.CommandType = CommandType.Text;
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

            }
        }

        public static void UpdateStockFileNumbers(string connectionString)
        {
            String update = @"Update StockFile
                                Set StkNumber = R.NewStkNumber
                                FROM StockFile S inner join LOTS_ReallocatedStockFiles R
                                on S.StkID = R.StkID

                                Update StockFileApns
                                Set StkNumber = R.NewStkNumber
                                FROM StockFileApns S inner join LOTS_ReallocatedStockFiles R
                                on S.StkID = R.StkID

                                Update StockFilePDES
                                Set StkNumber = R.NewStkNumber
                                FROM StockFilePDES S inner join LOTS_ReallocatedStockFiles R
                                on S.StkID = R.StkID

                                Update StockFileTurns
                                Set StkNumber = R.NewStkNumber
                                FROM StockFileTurns S inner join LOTS_ReallocatedStockFiles R
                                on S.StkID = R.StkID

                                Update StockFileTrans
                                Set StkNumber = R.NewStkNumber
                                FROM StockFileTrans S inner join LOTS_ReallocatedStockFiles R
                                on S.StkID = R.StkID

                                Update StockFileHistTot
                                Set StkNumber = R.NewStkNumber
                                FROM StockFileHistTot S inner join LOTS_ReallocatedStockFiles R
                                on S.StkID = R.StkID

                                Update StockFileHistLY
                                Set StkNumber = R.NewStkNumber
                                FROM StockFileHistLY S inner join LOTS_ReallocatedStockFiles R
                                on S.StkID = R.StkID

                                Update StockFileHistCurr
                                Set StkNumber = R.NewStkNumber
                                FROM StockFileHistCurr S inner join LOTS_ReallocatedStockFiles R
                                on S.StkID = R.StkID";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {

                SqlCommand command = new SqlCommand(update, connection);
                command.CommandTimeout = 3600;
                command.CommandType = CommandType.Text;
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

            }
        }

        public static void ClearStockFilePDEsDefault(string connectionString, String insideString)
        {
            String update = @"Update StockFilePDES
                                Set StkDefaultSupplier = 0
                                WHERE StkID in " + insideString;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(update, connection);
                command.CommandTimeout = 3600;
                command.CommandType = CommandType.Text;
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

            }
        }

        public static void SetStockFilePDEsDefault(string connectionString, List<Guid> ids)
        {
            while (ids.Count > 0)
            {
                //create batch for in clause
                List<Guid> batchIds = new List<Guid>();
                while (batchIds.Count < 1000 && ids.Count > 0)
                {
                    Guid id = ids[0];

                    batchIds.Add(id);
                    ids.RemoveAt(0);
                }

                if (batchIds.Count > 0)
                {
                    String update = @"Update StockFilePDES Set StkDefaultSupplier = 1 WHERE StkPDEID in (" + GetListAsStringForSQL(batchIds, true) + ")";
                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        SqlCommand command = new SqlCommand(update, connection);
                        command.CommandTimeout = 3600;
                        command.CommandType = CommandType.Text;
                        connection.Open();
                        command.ExecuteNonQuery();
                        connection.Close();
                    }
                }
            }
        }

        private static String GetListAsStringForSQL<T>(List<T> list, bool encloseValuesWithQuotes)
        {
            StringBuilder builder = new StringBuilder();
            bool started = false;
            foreach (T o in list)
            {
                if (!started)
                    started = true;
                else
                    builder.Append(", ");

                if (encloseValuesWithQuotes)
                    builder.Append("'");

                builder.Append(o.ToString());

                if (encloseValuesWithQuotes)
                    builder.Append("'");
            }

            return builder.ToString();
        }

        public static int PopulateStkNumber(String connectionString, Dictionary<int, Guid> ExistingStkNumbers)
        {

            String query = @"select StkNumber, StkID from StockFile";
            SqlConnection conn = null;
            SqlDataReader reader = null;

            int maxStockNumber = 0;
            conn = new SqlConnection(connectionString);
            conn.Open();

            SqlCommand command = new SqlCommand(query, conn);
            command.CommandTimeout = 3600;
            reader = command.ExecuteReader();

            while (reader.Read())
            {
                int stockNumber = reader.GetInt32(0);
                ExistingStkNumbers[stockNumber] = reader.GetGuid(1);

                if (stockNumber > maxStockNumber)
                    maxStockNumber = stockNumber;
            }

            if (reader != null)
                reader.Close();

            if (conn != null)
                conn.Close();

            return maxStockNumber;
        }

        public static void RemoveUpdatedStockFileNumberReferences(string connectionString, string insideString)
        {
            String update = @"Delete StockFileTurns WHERE StkID in " + insideString +
                              " Delete StockFileTrans WHERE StkID in " + insideString +
                              " Delete StockFileHistTot WHERE StkID in " + insideString +
                              " Delete StockFileHistLY WHERE StkID in " + insideString +
                              " Delete StockFileHistCurr WHERE StkID in " + insideString;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {

                SqlCommand command = new SqlCommand(update, connection);
                command.CommandTimeout = 3600;
                command.CommandType = CommandType.Text;
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
        }

        public static Guid? GetCPIAMStockFileMatch(List<long> Apns, List<Tuple<int, string>> SupplierPDEs, string Name, Dictionary<Guid, string> StockNames, Dictionary<Int64, Tuple<Guid, int>> StockAPNs, Dictionary<Tuple<int, string>, Tuple<Guid, Guid>> StockPDEs, Dictionary<int, int> SupplierDictionary)
        {
            float overallSim;
            bool numbersMatch;
            string stkName;
            //try find by APN
            HashSet<Guid> apnMatchStkIDs = new HashSet<Guid>();
            foreach (long apn in Apns)
            {
                if (StockAPNs.ContainsKey(apn))
                {
                    Guid stkID = StockAPNs[apn].Item1;
                    if (IsStockNameSimilar(stkID, Name, out overallSim, out numbersMatch, out stkName, StockNames))
                    {
                        return stkID;
                    }
                    else
                    {
                        apnMatchStkIDs.Add(stkID);
                    }
                }
            }

            //find by PDE
            foreach (Tuple<int, string> SupplierPDE in SupplierPDEs)
            {
                if (SupplierDictionary.ContainsKey(SupplierPDE.Item1))
                {
                    Tuple<int, string> supplierPDE = new Tuple<int, string>(SupplierDictionary[SupplierPDE.Item1], SupplierPDE.Item2);
                    if (StockPDEs.ContainsKey(supplierPDE))
                    {
                        Guid stkID = StockPDEs[supplierPDE].Item1;
                        if (apnMatchStkIDs.Contains(stkID))
                        {
                            //print out where name match is bad
                            IsStockNameSimilar(stkID, Name, out overallSim, out numbersMatch, out stkName, StockNames);

                            if (overallSim < 0.5)
                            {
                                ;
                            }
                            return stkID;
                        }
                        else if (IsStockNameSimilar(stkID, Name, out overallSim, out numbersMatch, out stkName, StockNames))
                        {
                            return stkID;
                        }
                        else
                        {
                            ;
                        }
                    }
                }
            }
            return null;
        }

        private static bool IsStockNameSimilar(Guid stkID, string LotsName, out float overallSim, out bool numbersMatch, out string stkName, Dictionary<Guid, string> StockNames)
        {
            StockNames.TryGetValue(stkID, out stkName);

            if (string.IsNullOrWhiteSpace(stkName))
            {
                overallSim = 0;
                numbersMatch = false;

                return false;
            }

            //float overallSim;
            float firstWordSim;
            //bool numbersMatch;
            FuzzyWordMatcher.GetInstance().DetermineSimilarity(LotsName, stkName, true, out overallSim, out firstWordSim, out numbersMatch);
            return numbersMatch && overallSim > 0.7;    //take 70% similarity as good enough
        }
    }

    public class StockFileHistTot
    {
        public StockFileHistTot(Guid StkID, int StkNumber)
        {
            this.StkID = StkID;
            this.StkNumber = StkNumber;
        }

        private Guid _StkID;

        public Guid StkID
        {
            get { return _StkID; }
            set { _StkID = value; }
        }
        private int _StkNumber;

        public int StkNumber
        {
            get { return _StkNumber; }
            set { _StkNumber = value; }
        }
        public static void SaveToDateTable(StockFileHistTot stockFileHistTot, DataTable StockFileHistTotDT)
        {
            StockFileHistTotDT.Rows.Add(stockFileHistTot.StkID, stockFileHistTot.StkNumber);
        }
    }

    public class StockFileTrans
    {
        public StockFileTrans(Guid StkID, int StkNumber)
        {
            this.StkID = StkID;
            this.StkNumber = StkNumber;
        }

        private Guid _StkID;

        public Guid StkID
        {
            get { return _StkID; }
            set { _StkID = value; }
        }
        private int _StkNumber;

        public int StkNumber
        {
            get { return _StkNumber; }
            set { _StkNumber = value; }
        }

        public static void SaveToDateTable(StockFileTrans StockFileTrans, DataTable StockFileTransDT)
        {
            StockFileTransDT.Rows.Add(StockFileTrans.StkID,StockFileTrans.StkNumber);
        }
    }

    public class StockFileTurns
    {
        public StockFileTurns(Guid StkID, int StkNumber)
        {
            this.StkID = StkID;
            this.StkNumber = StkNumber;
        }
        private Guid _StkID;

        public Guid StkID
        {
            get { return _StkID; }
            set { _StkID = value; }
        }
        private int _StkNumber;

        public int StkNumber
        {
            get { return _StkNumber; }
            set { _StkNumber = value; }
        }

        public static void SaveToDateTable(StockFileTurns StockFileTurns, DataTable StockFileTurnsDT)
        {
            StockFileTurnsDT.Rows.Add(StockFileTurns.StkID, StockFileTurns.StkNumber);
        }
    }

    public class StockFileHistCurr
    {
        public StockFileHistCurr(Guid StkID,int StkNumber)
        {
            this.StkID = StkID;
            this.StkNumber = StkNumber;
        }
        private Guid _StkID;

        public Guid StkID
        {
            get { return _StkID; }
            set { _StkID = value; }
        }
        private int _StkNumber;

        public int StkNumber
        {
            get { return _StkNumber; }
            set { _StkNumber = value; }
        }

        public static void SaveToDateTable(StockFileHistCurr stockFileHistCurr, DataTable stockFileHistCurrDT)
        {
            stockFileHistCurrDT.Rows.Add(stockFileHistCurr.StkID, stockFileHistCurr.StkNumber);
        }
    }

    public class StockFileHistLY
    {
        public StockFileHistLY(Guid StkID, int StkNumber)
        {
            this.StkID = StkID;
            this.StkNumber = StkNumber;
        }
        private Guid _StkID;

        public Guid StkID
        {
            get { return _StkID; }
            set { _StkID = value; }
        }
        private int _StkNumber;

        public int StkNumber
        {
            get { return _StkNumber; }
            set { _StkNumber = value; }
        }

        public static void SaveToDateTable(StockFileHistLY StockFileHistLY, DataTable stockFileHistLYDT)
        {
            stockFileHistLYDT.Rows.Add(StockFileHistLY.StkID, StockFileHistLY.StkNumber);
        }
    }

    public class StockPromotions
    {
        public StockPromotions(Guid StkID, DateTime StkBasicPromoStart, DateTime StkBasicPromoEnd, int StkBasicPromoQty, Decimal StkBasicPromoPrice, string stkPromoDesc)
        {
            //this.StkPromoID = StkPromoID;
            this.StkID = StkID;
            this.StkBasicPromoStart = StkBasicPromoStart;
            this.StkBasicPromoEnd = StkBasicPromoEnd;
            this.StkBasicPromoQty = StkBasicPromoQty;
            this.StkBasicPromoPrice = StkBasicPromoPrice;
            this.StkPromoDesc = stkPromoDesc;
        }
        private Guid stkPromoID;

        public Guid StkPromoID
        {
            get { return stkPromoID; }
            set { stkPromoID = value; }
        }

        private Guid _StkID;

        public Guid StkID
        {
            get { return _StkID; }
            set { _StkID = value; }
        }
        private DateTime _stkBasicPromoStart;

        public DateTime StkBasicPromoStart
        {
            get { return _stkBasicPromoStart; }
            set { _stkBasicPromoStart = value; }
        }
        private DateTime _stkBasicPromoEnd;

        public DateTime StkBasicPromoEnd
        {
            get { return _stkBasicPromoEnd; }
            set { _stkBasicPromoEnd = value; }
        }
        private int _stkBasicPromoQty;

        public int StkBasicPromoQty
        {
            get { return _stkBasicPromoQty; }
            set { _stkBasicPromoQty = value; }
        }
        private Decimal _stkBasicPromoPrice;

        public Decimal StkBasicPromoPrice
        {
            get { return _stkBasicPromoPrice; }
            set { _stkBasicPromoPrice = value; }
        }

        private string _stkPromoDesc;

        public string StkPromoDesc
        {
            get { return _stkPromoDesc; }
            set
            {
                _stkPromoDesc = string.IsNullOrWhiteSpace(value) ? "" : value;
                if (_stkPromoDesc.Length > 150)
                    _stkPromoDesc = _stkPromoDesc.Substring(0, 150);
            }
        }

        public static void SaveToDateTable(StockPromotions stockPromotions, DataTable StockPromotionsDT) 
        {
            StockPromotionsDT.Rows.Add(stockPromotions.StkID, stockPromotions.StkBasicPromoStart, stockPromotions.StkBasicPromoEnd, stockPromotions.StkBasicPromoQty, stockPromotions.StkBasicPromoPrice, stockPromotions.StkPromoDesc);
        }
    }

    public class StockFileLocCat
    {
        public StockFileLocCat(Guid StkCatLocID,Guid StkID,string StkLocCat,Boolean StkIsLoc)
        {
            this.StkCatLocID = StkCatLocID;
            this.StkID = StkID;
            this.StkLocCat = StkLocCat;
            this.StkIsLoc = StkIsLoc;
        }
        private Guid _StkCatLocID;

        public Guid StkCatLocID
        {
            get { return _StkCatLocID; }
            set { _StkCatLocID = value; }
        }

        private Guid _StkID;

        public Guid StkID
        {
            get { return _StkID; }
            set { _StkID = value; }
        }
        private string _StkLocCat;

        public string StkLocCat
        {
            get { return _StkLocCat; }
            set
            {
                _StkLocCat = string.IsNullOrWhiteSpace(value) ? "" : value;
                if (_StkLocCat.Length > 100)
                    _StkLocCat = _StkLocCat.Substring(0, 100);
            }
        }
        private Boolean _StkIsLoc;

        public Boolean StkIsLoc
        {
            get { return _StkIsLoc; }
            set { _StkIsLoc = value; }
        }


        public static void SaveToDateTable(StockFileLocCat stockFileLocCat, DataTable StockFileLocCatDT)
        {
            StockFileLocCatDT.Rows.Add(stockFileLocCat.StkCatLocID, stockFileLocCat.StkID,stockFileLocCat.StkLocCat,stockFileLocCat.StkIsLoc);
        }
    }

    public class StockFileApns
    {
        public StockFileApns(Guid StkAPNID, Guid StkID, int StkNumber, string StkApn)
        {
            this.StkNumber = StkNumber;
            this.StkApn = StkApn;
            this.StkID = StkID;
            this.StkAPNID = StkAPNID;
        }
        private int _StkNumber;

        public int StkNumber
        {
            get { return _StkNumber; }
            set { _StkNumber = value; }
        }
        private string _StkApn;

        public string StkApn
        {
            get { return _StkApn; }
            set
            {
                _StkApn = string.IsNullOrWhiteSpace(value) ? "" : value;
                if (_StkApn.Length > 50)
                    _StkApn = _StkApn.Substring(0, 50);
            }
        }
        private Guid _StkID;

        public Guid StkID
        {
            get { return _StkID; }
            set { _StkID = value; }
        }
        private Guid _StkAPNID;

        public Guid StkAPNID
        {
            get { return _StkAPNID; }
            set { _StkAPNID = value; }
        }

        public static void SaveToDateTable(StockFileApns stockFileApns, DataTable StockFileApnsDT)
        {
            StockFileApnsDT.Rows.Add(stockFileApns.StkAPNID,stockFileApns.StkID,stockFileApns.StkNumber,stockFileApns.StkApn);
        }

        //dictionary APN -> <StkID, Stock Number>
        public static void PopulateStockAPNs(String cpiamConnString, Dictionary<Int64, Tuple<Guid, int>> StockAPNs, Dictionary<Guid, List<String>> StockAPNsByID)
        {
            String query = @"select StkApn, StkID, StkNumber from stockfileapns";
            SqlConnection conn = null;
            SqlDataReader reader = null;

            conn = new SqlConnection(cpiamConnString);
            conn.Open();

            SqlCommand command = new SqlCommand(query, conn);
            command.CommandTimeout = 3600;
            reader = command.ExecuteReader();
            Int64 test;
            while (reader.Read())
            {
                string apn = reader.GetString(0);
                Guid StkID = reader.GetGuid(1);

                if (Int64.TryParse(apn, out test))
                {
                    StockAPNs[test] = new Tuple<Guid, int>(reader.GetGuid(1), reader.GetInt32(2));
                }

                if (!StockAPNsByID.ContainsKey(StkID))
                    StockAPNsByID[StkID] = new List<string>();

                StockAPNsByID[StkID].Add(apn);
            }

            if (reader != null)
                reader.Close();

            if (conn != null)
                conn.Close();
        }
    }

    public class StockFilePDES
    {
        public StockFilePDES(Guid StkPDEID, Guid StkID, int StkNumber, string StkPDE, int StkSupplierID, string StkSupplierDesc, Boolean StkDefaultSupplier)
        {
            this.StkNumber = StkNumber;
            this.StkPDE = StkPDE;
            this.StkSupplierID = StkSupplierID;
            this.StkSupplierDesc = StkSupplierDesc;
            this.StkDefaultSupplier = StkDefaultSupplier;
            this.StkID = StkID;
            this.StkPDEID = StkPDEID;
        }

        private int _StkNumber;

        public int StkNumber
        {
            get { return _StkNumber; }
            set { _StkNumber = value; }
        }
        private string _StkPDE;

        public string StkPDE
        {
            get { return _StkPDE; }
            set
            {
                _StkPDE = string.IsNullOrWhiteSpace(value) ? "" : value;
                if (_StkPDE.Length > 50)
                    _StkPDE = _StkPDE.Substring(0, 50);
            }
        }
        private int _StkSupplierID;

        public int StkSupplierID
        {
            get { return _StkSupplierID; }
            set { _StkSupplierID = value; }
        }
        private string _StkSupplierDesc;

        public string StkSupplierDesc
        {
            get { return _StkSupplierDesc; }
            set
            {
                _StkSupplierDesc = string.IsNullOrWhiteSpace(value) ? "" : value;
                if (_StkSupplierDesc.Length > 100)
                    _StkSupplierDesc = _StkSupplierDesc.Substring(0, 100);
            }
        }
        private Boolean _StkDefaultSupplier;

        public Boolean StkDefaultSupplier
        {
            get { return _StkDefaultSupplier; }
            set { _StkDefaultSupplier = value; }
        }
        private Guid _StkID;

        public Guid StkID
        {
            get { return _StkID; }
            set { _StkID = value; }
        }
        private Guid _StkPDEID;

        public Guid StkPDEID
        {
            get { return _StkPDEID; }
            set { _StkPDEID = value; }
        }

        public static void SaveToDateTable(StockFilePDES stockFilePDES, DataTable StockFilePDESDT)
        {
            StockFilePDESDT.Rows.Add(stockFilePDES.StkPDEID,stockFilePDES.StkID,stockFilePDES.StkNumber,stockFilePDES.StkPDE,stockFilePDES.StkSupplierID,stockFilePDES.StkSupplierDesc,stockFilePDES.StkDefaultSupplier);
        }

        //dictionary APN -> <StkID, StkPDEID>
        public static void PopulateStockPDEs(String cpiamConnString, Dictionary<Tuple<int, string>, Tuple<Guid, Guid>> StockPDEs)
        {

            String query = @"select StkSupplierID, StkPDE, StkID, StkPDEID from StockFilePDES";
            SqlConnection conn = null;
            SqlDataReader reader = null;
            conn = new SqlConnection(cpiamConnString);
            conn.Open();

            SqlCommand command = new SqlCommand(query, conn);
            command.CommandTimeout = 3600;
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                int supplierID = reader.GetInt32(0);
                string pde = reader.GetString(1);
                Tuple<int, string> supplierPde = new Tuple<int, string>(supplierID, pde);
                StockPDEs[supplierPde] = new Tuple<Guid, Guid>(reader.GetGuid(2), reader.GetGuid(3));
            }

            if (reader != null)
                reader.Close();

            if (conn != null)
                conn.Close();
        }

    }


    public class Purchase
    {
        public float PurQty;
        public decimal Cost;
    }

    public class HistPurchase
    {
        public float PurMth1;
        public decimal CostMth1;

        public float PurMth2;
        public decimal CostMth2;

        public float PurMth3;
        public decimal CostMth3;

        public float PurMth4;
        public decimal CostMth4;

        public float PurMth5;
        public decimal CostMth5;

        public float PurMth6;
        public decimal CostMth6;

        public float PurMth7;
        public decimal CostMth7;

        public float PurMth8;
        public decimal CostMth8;

        public float PurMth9;
        public decimal CostMth9;

        public float PurMth10;
        public decimal CostMth10;

        public float PurMth11;
        public decimal CostMth11;

        public float PurMth12;
        public decimal CostMth12;
    }

    public class Sale
    {
        public float SoldQty;
        public decimal SellVal;
        public decimal COGS;
    }

    public class HistSales
    {
        public float SoldMth1;
        public decimal SellMth1;
        public decimal COGSMth1;

        public float SoldMth2;
        public decimal SellMth2;
        public decimal COGSMth2;

        public float SoldMth3;
        public decimal SellMth3;
        public decimal COGSMth3;

        public float SoldMth4;
        public decimal SellMth4;
        public decimal COGSMth4;

        public float SoldMth5;
        public decimal SellMth5;
        public decimal COGSMth5;

        public float SoldMth6;
        public decimal SellMth6;
        public decimal COGSMth6;

        public float SoldMth7;
        public decimal SellMth7;
        public decimal COGSMth7;

        public float SoldMth8;
        public decimal SellMth8;
        public decimal COGSMth8;

        public float SoldMth9;
        public decimal SellMth9;
        public decimal COGSMth9;

        public float SoldMth10;
        public decimal SellMth10;
        public decimal COGSMth10;

        public float SoldMth11;
        public decimal SellMth11;
        public decimal COGSMth11;

        public float SoldMth12;
        public decimal SellMth12;
        public decimal COGSMth12;
    }

    public class StockFileDetail
    {
        public StockFileDetail(int StkNo, int DeptNo, string GSTCode, string Description)
        {
            this.StkNo = StkNo;
            this.DeptNo = DeptNo;
            this.GSTCode = GSTCode;
            this.Description = Description;
        }

        private int _DeptNo;

        public int DeptNo
        {
            get { return _DeptNo; }
            set { _DeptNo = value; }
        }
        private int _StkNo;

        public int StkNo
        {
            get { return _StkNo; }
            set { _StkNo = value; }
        }
        private String _GSTCode;

        public String GSTCode
        {
            get { return _GSTCode; }
            set { _GSTCode = value; }
        }
        private String _Description;

        public String Description
        {
            get { return _Description; }
            set { _Description = value; }
        }
    }
}
